package it.unitn.science.matr155863_148346_145963.bccastestore;


import it.unitn.science.matr155863_148346_145963.bccastestore.items.Asta;
import it.unitn.science.matr155863_148346_145963.bccastestore.items.Category;
import it.unitn.science.matr155863_148346_145963.bccastestore.items.Image;
import it.unitn.science.matr155863_148346_145963.bccastestore.items.Offer;
import it.unitn.science.matr155863_148346_145963.bccastestore.items.User;
import it.unitn.science.matr155863_148346_145963.bccastestore.items.Product;
import it.unitn.science.matr155863_148346_145963.bccastestore.items.UM;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DBManager{
    
  private final String DB_DRIVER = "com.mysql.jdbc.Driver";
  private Connection con=null;
  /*
   * addAsta Usato per evitare aggiungere piu' aste sullo stesso prodotto senza
   * senza prima aver diminuito la quantita' di prodotto disponibile
   */
  private static Object addAsta;
  private static Object addOffer;
  
  public DBManager(String dbUrl,String dbuser,String dbpsw){
    try{
          Class.forName(DB_DRIVER);
          con=DriverManager.getConnection(dbUrl,dbuser,dbpsw);
          addAsta=new Object();
          addOffer=new Object();  
    }catch(Exception ex){
          throw new RuntimeException(ex);
    }
  }
    
    public void close() throws SQLException {
        if(!con.isClosed()){
            con.close();
        }
    }
    
    public User authenticate(String username,String password){
        User user=null;
        try{
            PreparedStatement pst=con.prepareStatement("SELECT * FROM user WHERE "
                    + User.USERNAME+"=? AND "
                    + User.PASSWORD+"=?");
            pst.setString(1, username);
            pst.setString(2, password);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                user=new User(rs.getString(User.EMAIL),
                        rs.getString(User.USERNAME),
                        null,//Password
                        rs.getString(User.FIRST_NAME),
                        rs.getString(User.LAST_NAME),
                        rs.getDate(User.BIRTHDATE).getTime(),
                        rs.getString(User.ADDRESS),
                        rs.getInt(User.STATUS),
                        rs.getInt(User.ID),
                        rs.getTimestamp(User.REGISTER_AT).getTime()
                        );
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        return user;
    }
    
    public User getUser(String username){
        User user=null;
        try{
            PreparedStatement pst=con.prepareStatement("SELECT * FROM user WHERE "
                    + User.USERNAME+"=?" );
            pst.setString(1, username);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                user=new User(rs.getString(User.EMAIL),
                        rs.getString(User.USERNAME),
                        null,//Password
                        rs.getString(User.FIRST_NAME),
                        rs.getString(User.LAST_NAME),
                        rs.getDate(User.BIRTHDATE).getTime(),
                        rs.getString(User.ADDRESS),
                        rs.getInt(User.STATUS),
                        rs.getInt(User.ID),
                        rs.getTimestamp(User.REGISTER_AT).getTime()
                        );
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        return user;
    }

    
    public Product getProduct(Integer productId){
        Product product=null;
        try{
            PreparedStatement pst=con.prepareStatement("SELECT "
                    + "P._id_product, "
                    + "P.name, "
                    + "P.quantity, "
                    + "P.um AS um_id, "
                    + "um.name AS um_name, "
                    + "I._id_image AS image_id,"
                    + "I.name AS image_name,"
                    + "P.description,"
                    + "P.seller AS seller_id,"
                    + "P.category AS category_id,"
                    + " username AS seller_name,"
                    + "C.name AS category_name "
                    + " FROM product P, user , category C, image I, um"
                    + " WHERE _id_product=? AND P.image=I._id_image "
                    + "AND P.seller=_id_user AND P.category=_id_category AND"
                    + " P.um=_id_um" );
            pst.setInt(1, productId);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
              product=new Product(
                      rs.getInt("_id_product"),
                      rs.getString("name"),
                      rs.getInt("quantity"),
                      rs.getInt("um_id"),
                      rs.getString("um_name"),
                      rs.getInt("image_id"),
                      rs.getString("image_name"),
                      rs.getString("description"),
                      rs.getInt("seller_id"),
                      rs.getString("seller_name"),
                      rs.getInt("category_id"),
                      rs.getString("category_name")
                      );
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        return product;
    }

    public int getProductId(String trackName,Integer authorId){
        Integer trackId=null;
        try{
            PreparedStatement pst=con.prepareStatement("SELECT _id_product FROM product "
                    + "WHERE name=? AND seller=?" );
            pst.setString(1, trackName);
            pst.setInt(2, authorId);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                trackId=rs.getInt("_id_product");
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        return trackId;
    }

    public Integer addProduct(Product product) {
        Integer productId=null;
        try{
            //con.startTransuction()
            con.setAutoCommit(false);
            //Devo aggiornare la tabella, ho camibato il campo name
            PreparedStatement pst=con.prepareStatement("INSERT INTO product"
                    + "(name, "
                    + "quantity,"
                    + "um ,"
                    
                    + "image ,"
                    
                    + "description ,"
                    + "seller ,"
                    + "category )"
                    + " VALUES ( ?,?,?,  ?,  ?,?,? );",
                    PreparedStatement.RETURN_GENERATED_KEYS);
            pst.setString(1, product.getName());
            pst.setInt(2, product.getQuantity());
            pst.setInt(3, product.getUmId());
            pst.setInt(4, product.getImageId());
            pst.setString(5, product.getDescription());
            pst.setInt(6, product.getSellerId());
            pst.setInt(7, product.getCategoryId());
            pst.executeUpdate();
            ResultSet rs = pst.getGeneratedKeys();
            if (rs != null){
                try{
                    if (rs.next()) {
                        productId=rs.getInt(1);
                    }
                } finally {
                        rs.close();
                }
            }
            con.commit();
            //=getProductId(product.getName(), product.getSellerId());
        }catch(SQLException ex){
            String message=ex.toString();
            if(ex.getErrorCode()==1062){
                message="There is already a product with this name, change it.";
            }
            try{
                con.rollback();
            } catch (SQLException ex1) {
                message=ex1.toString();
            }
            throw new RuntimeException(message);
        }finally{
            try{
                if (!con.getAutoCommit()) {
                    con.setAutoCommit(true);
                }
            }catch(Exception ex){
                throw new RuntimeException("Error diuring autocommit");
            }
        }
        return productId;
    }
    
    public String categoryExist(Integer category) {
        String name=null;
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT name"
                    + " FROM category WHERE _id_category=?");
            pst.setInt(1, category);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                name=rs.getString("name");
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return name;
    }
    
    public ArrayList<Category> getCategories() {
        ArrayList<Category> categories=new ArrayList<Category>();
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT * FROM category ");
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                Category cat=new Category(rs.getInt("_id_category"),
                        rs.getString("name"));
                categories.add(cat);
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return categories;
    }
    
    public String umExist(Integer um) {
        String name=null;
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT name"
                    + " FROM um WHERE _id_um=?");
            pst.setInt(1, um);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                name=rs.getString("name");
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return name;
    }
    
    public ArrayList<UM> getUMs() {
        ArrayList<UM> ums=new ArrayList<UM>();
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT * FROM um ");
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                UM um=new UM(rs.getInt("_id_um"),
                        rs.getString("name"));
                ums.add(um);
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return ums;
    }
    
    public String sellerExist(Integer seller) {
        String name=null;
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT (first_name|| ' ' || last_name) AS name"
                    + " FROM user WHERE _id_user=?");
            pst.setInt(1, seller);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                name=rs.getString("name");
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return name;
    }

    public ArrayList<Image> getImages() {
        ArrayList<Image> images=new ArrayList<Image>();
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT * FROM image ");
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                Image image=new Image(rs.getInt("_id_image"),rs.getString("name"));
                images.add(image);
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return images;
    }

    public Image getImage(int imageId) {
        Image image=null;
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT * FROM image WHERE _id_image=?");
            pst.setInt(1, imageId);
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                image=new Image(rs.getInt("_id_image"),rs.getString("name"));
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return image;
    }

    public Product setQuantityProduct(int idInt,int quantita) {
         try {
            PreparedStatement pst=con.prepareStatement(
                            "UPDATE product "
                          + "SET quantity=? "
                          + "WHERE _id_product=? ");
            pst.setInt(1, quantita);
            pst.setInt(2, idInt);
            pst.executeUpdate();
            return getProduct(idInt);
            /*while(rs.next()){
                image=new Image(rs.getInt("_id_image"),rs.getString("name"));
            }*/
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
    }

    public User getSellerOf(int idProd) {
        User user=null;
        try{
            PreparedStatement pst=con.prepareStatement("SELECT * FROM user WHERE "
                    + "_id_user=(SELECT seller FROM product WHERE _id_product=?)");
            pst.setInt(1, idProd);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                user=new User(rs.getString(User.EMAIL),
                        rs.getString(User.USERNAME),
                        null,//Password
                        rs.getString(User.FIRST_NAME),
                        rs.getString(User.LAST_NAME),
                        rs.getDate(User.BIRTHDATE).getTime(),
                        rs.getString(User.ADDRESS),
                        rs.getInt(User.STATUS),
                        rs.getInt(User.ID),
                        rs.getTimestamp(User.REGISTER_AT).getTime()
                        );
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        return user;
    }

   

    private PreparedStatement prepareRegisterStatement() throws SQLException {
        String query = "INSERT INTO user("
                + " " + User.USERNAME + ","
                + " " + User.EMAIL + ","
                + " " + User.PASSWORD + ","
                + " " + User.FIRST_NAME + ","
                + " " + User.LAST_NAME + ","
                + " " + User.BIRTHDATE + ","
                + " " + User.ADDRESS + ","
                + " " + User.STATUS + ","
                + " " + User.REGISTER_AT + ") "
                + " VALUES (?,?,?,?,?,?,?,?,?);";
        return con.prepareStatement(query);
    }
    
    public User registerUser(User user) {
        try {
            PreparedStatement pst = prepareRegisterStatement();
            pst.setString(1, user.getUsername());
            String email=user.getEmail().toLowerCase();
            pst.setString(2, email);
            pst.setString(3, user.getPassword());
            pst.setString(4, user.getFirst_name());
            pst.setString(5, user.getLast_name());
            java.sql.Date birthdate = new java.sql.Date(user.getBirthdate().getTime());
            pst.setDate(6, birthdate);
            pst.setString(7, user.getAddress());
            pst.setInt(8,user.getStatus());//Codice da convalidare
            java.sql.Timestamp register_at = new java.sql.Timestamp(new Date().getTime());
            pst.setTimestamp(9, register_at);
            int n=pst.executeUpdate();
            if(n==0){
                return null;//NOn ha inserito nulla
            }else{
                return this.getUser(user.getUsername());
            }
        } catch (SQLException ex) {
            if (ex.getErrorCode() == 1062) {
                throw new RuntimeException("You are already register");
            } else {
                throw new RuntimeException("Unable to execute the registration!");
            }
        }
    }
    

    public Integer addAsta(Asta asta) {
        Integer astaId=null;
        synchronized(addAsta){
        try{
            //con.startTransuction()
            con.setAutoCommit(false);
            PreparedStatement pst=con.prepareStatement("INSERT INTO asta"
                    + "("
                    + "seller,"
                    + "product,"
                    + "quantity,"
                    
                    + "start_price, "
                    + "min_price, "
                    + "min_increment, "
                    + "shipping_price,"
                    
                    + "date,"
                    + "um ,"
                    + "annulled )"
                    + " VALUES (?,?,?, ?,?,?,?  ,?,?,?);",
                    PreparedStatement.RETURN_GENERATED_KEYS);
            
            pst.setInt(1, asta.getSellerId());
            pst.setInt(2, asta.getProductId());
            pst.setInt(3, asta.getQuantity());
            
            pst.setFloat(4, asta.getStart_price());
            pst.setFloat(5, asta.getMin_price());
            pst.setFloat(6, asta.getMin_increment());
            pst.setFloat(7, asta.getShippingPrice());
           
            //per inserire sia la data che il tempo:
            java.sql.Timestamp datetime = new java.sql.Timestamp(asta.getDate().getTime());
                        
            pst.setObject(8,datetime);
            pst.setInt(9,asta.getUm());
            pst.setBoolean(10,asta.getAnnulled());
            
            pst.executeUpdate();
  
            ResultSet rs = pst.getGeneratedKeys();
            if (rs != null){
                try{
                    if (rs.next()) {
                        astaId=rs.getInt(1);
                    }
                } finally {
                        rs.close();
                }
            }
            int idProdotto=asta.getProductId();
            int quantitaRimastaProdotto=asta.getProduct().getQuantity()-asta.getQuantity();
            setQuantityProduct(idProdotto, quantitaRimastaProdotto);
            asta.getProduct().setQuantity(quantitaRimastaProdotto);
            con.commit();
            //=getProductId(product.getName(), product.getSellerId());
        }catch(SQLException ex){
            ex.printStackTrace();
            String message=ex.toString();
            if(ex.getErrorCode()==1062){
                message="There is already a asta with this name, change it.";
            }
            try{
                con.rollback();
            } catch (SQLException ex1) {
                message=ex1.toString();
            }
            throw new RuntimeException(message);
        }finally{
            try{
                if (!con.getAutoCommit()) {
                    con.setAutoCommit(true);
                }
            }catch(Exception ex){
                throw new RuntimeException("Error diuring autocommit");
            }
        }
        }
        return astaId;
    }

    public String userExist(String email) {
        try{
            email=email.toLowerCase();
            PreparedStatement pst=con.prepareStatement("SELECT username FROM user WHERE email=?");
            pst.setString(1, email);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                return rs.getString("username");
            }else{
                return null;
            }
        }catch(Exception ex){
            throw new RuntimeException("Unable to active the user");
        }
    }

    public void setNewPasword(String username,String password) {
        try{
            PreparedStatement pst=con.prepareStatement("UPDATE user SET password=? WHERE username=?");
            pst.setString(1, password);
            pst.setString(2, username);
            pst.executeUpdate();
        }catch(Exception ex){
            throw new RuntimeException("Unable to active the user");
        }
    }
 
    public int addImage(String name) {
        try{   
            String query = "INSERT INTO image("
                    + " name ) "
                    + " VALUES (?);";
            PreparedStatement pst=con.prepareStatement(query);
            pst.setString(1, name);
            pst.executeUpdate();
            return this.getId_image(name);
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        
    }

    private Integer getId_image(String name) {
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT _id_image FROM image WHERE name=?");
            pst.setString(1, name);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                return rs.getInt("_id_image");
            }else{
                return null;
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
    }

    public boolean activeUser(Integer code, Long register_at) {
        try{
            PreparedStatement pst=con.prepareStatement("UPDATE user SET status=1 WHERE status=? AND register_at=?");
            pst.setInt(1, code);
            java.sql.Timestamp ts=new java.sql.Timestamp(register_at);
            pst.setTimestamp(2, ts);
            int nAc=pst.executeUpdate();
            if(nAc>0){
                return true;
            }else{
                return false;
            }
        }catch(Exception ex){
            throw new RuntimeException("Unable to active the user");
        }
    }

    public Asta getAsta(Integer astaId){
        Asta asta=null;
        try{
            System.out.println("sn in getAsta (by id)");
            PreparedStatement pst=con.prepareStatement("SELECT * "                    
                    + " FROM asta"
                    + " WHERE _id_asta=? " );
            pst.setInt(1, astaId);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
              asta=new Asta(
                      rs.getInt("_id_asta"),
                      rs.getInt("buyer"),
                      rs.getInt("seller"),
                      rs.getInt("product"),
                      rs.getInt("quantity"),
                      
                      rs.getFloat("start_price"),
                      rs.getFloat("min_price"),
                      rs.getFloat("min_increment"),
                      rs.getFloat("shipping_price"),
                      rs.getFloat("last_offer_price"),
                      
                      (Date)rs.getObject("date"),
                      rs.getInt("um"),
                      rs.getBoolean("annulled"),
                      null
                      );
              asta.setProduct(this.getProduct(asta.getProductId()));
              asta.setSeller(this.getUser(asta.getSellerId()));
              if(asta.getBuyerId()!=null){
                  asta.setBuyer(this.getUser(asta.getBuyerId()));
              }
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        return asta;
    }

     
    public UM getUm(Integer umId){
        UM um=null;
        try{
            
            PreparedStatement pst=con.prepareStatement("SELECT * "                    
                    + " FROM um"
                    + " WHERE _id_um=? " );
            pst.setInt(1, umId);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
              um=new UM(
                      rs.getInt("_id_um"),
                      rs.getString("name")
                      );
               System.out.println("um trovataaaa ");
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        return um;
    }
    
    
 
    public ArrayList<Product> getProductFromUser(Integer id_user){
        ArrayList<Product> products=new ArrayList<Product>();
        try {
            PreparedStatement pst=con.prepareStatement("SELECT "
                    + "P._id_product, "
                    + "P.name, "
                    + "P.quantity, "
                    + "P.um AS um_id, "
                    + "um.name AS um_name, "
                    + "I._id_image AS image_id,"
                    + "I.name AS image_name,"
                    + "P.description,"
                    + "P.seller AS seller_id,"
                    + "P.category AS category_id,"
                    + " username AS seller_name,"
                    + "C.name AS category_name "
                    + " FROM product P, user , category C, image I, um"
                    + " WHERE seller=? AND P.image=I._id_image"
                    + " AND user._id_user=seller AND P.category=_id_category AND"
                    + " P.um=_id_um" );
            
            pst.setInt(1, id_user);
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                Product prod=new Product(
                      rs.getInt("_id_product"),
                      rs.getString("name"),
                      rs.getInt("quantity"),
                      rs.getInt("um_id"),
                      rs.getString("um_name"),
                      rs.getInt("image_id"),
                      rs.getString("image_name"),
                      rs.getString("description"),
                      rs.getInt("seller_id"),
                      rs.getString("seller_name"),
                      rs.getInt("category_id"),
                      rs.getString("category_name")
                      );
                products.add(prod);
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return products;
    }

    public User getUser(Integer id) {
        User user=null;
        try{
            PreparedStatement pst=con.prepareStatement("SELECT * FROM user WHERE "
                    + User.ID+"=?" );
            pst.setInt(1, id);
            ResultSet rs=pst.executeQuery();
            if(rs.next()){
                user=new User(rs.getString(User.EMAIL),
                        rs.getString(User.USERNAME),
                        null,//Password
                        rs.getString(User.FIRST_NAME),
                        rs.getString(User.LAST_NAME),
                        rs.getDate(User.BIRTHDATE).getTime(),
                        rs.getString(User.ADDRESS),
                        rs.getInt(User.STATUS),
                        rs.getInt(User.ID),
                        rs.getTimestamp(User.REGISTER_AT).getTime()
                        );
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }
        return user;
    }

    public boolean annullaAsta(Integer idAsta) {
        try {
            PreparedStatement pst=con.prepareStatement(
                            "UPDATE asta "
                          + "SET annulled=? "
                          + "WHERE _id_asta=?" );
            pst.setBoolean(1, true);
            pst.setInt(2, idAsta);
            pst.executeUpdate();
            return true;
            /*while(rs.next()){
                image=new Image(rs.getInt("_id_image"),rs.getString("name"));
            }*/
        } catch (SQLException ex) {
            
            throw new RuntimeException(ex);
            
        }
    }

    public ArrayList<Asta> getAsteCorrenti() {
        ArrayList<Asta> aste=new ArrayList<Asta>();
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT * FROM asta WHERE annulled = ? AND date>? ");
            pst.setBoolean(1, false);
            pst.setTimestamp(2, new java.sql.Timestamp(new Date().getTime()));
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                Asta asta=new Asta(
                      rs.getInt("_id_asta"),
                      rs.getInt("buyer"),
                      rs.getInt("seller"),
                      rs.getInt("product"),
                      rs.getInt("quantity"),
                      
                      rs.getFloat("start_price"),
                      rs.getFloat("min_price"),
                      rs.getFloat("min_increment"),
                      rs.getFloat("shipping_price"),
                      rs.getFloat("last_offer_price"),
                      
                      (Date)rs.getObject("date"),
                      rs.getInt("um"),
                      rs.getBoolean("annulled"),
                      null
                      );
                asta.setProduct(this.getProduct(asta.getProductId()));
                asta.setSeller(this.getUser(asta.getSellerId()));
                if(asta.getBuyerId()!=null){
                    asta.setBuyer(this.getUser(asta.getBuyerId()));
                }
                aste.add(asta);
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return aste;
    }
    
    public List<Asta> getAsteAggiudigate(){
        ArrayList<Asta> aste=new ArrayList<Asta>();
        try {
            PreparedStatement pst=con.prepareStatement(
                            "SELECT * FROM asta WHERE annulled = ? AND date<? "
                            + " AND min_price<=last_offer_price");
            pst.setBoolean(1, false);
            pst.setTimestamp(2, new java.sql.Timestamp(new Date().getTime()));
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                Asta asta=new Asta(
                      rs.getInt("_id_asta"),
                      rs.getInt("buyer"),
                      rs.getInt("seller"),
                      rs.getInt("product"),
                      rs.getInt("quantity"),
                      
                      rs.getFloat("start_price"),
                      rs.getFloat("min_price"),
                      rs.getFloat("min_increment"),
                      rs.getFloat("shipping_price"),
                      rs.getFloat("last_offer_price"),
                      
                      (Date)rs.getObject("date"),
                      rs.getInt("um"),
                      rs.getBoolean("annulled"),
                      null
                      );
                asta.setProduct(this.getProduct(asta.getProductId()));
                asta.setSeller(this.getUser(asta.getSellerId()));
                if(asta.getBuyerId()!=null){
                    asta.setBuyer(this.getUser(asta.getBuyerId()));
                }
                aste.add(asta);
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        return aste;
    }

    public Integer editProductDescriptionQuantity(Product product) throws Exception {
        String query="UPDATE product SET quantity=? , description=? WHERE _id_product=?";
        try{
            PreparedStatement pst=con.prepareStatement(query);
            pst.setInt(1, product.getQuantity());
            pst.setString(2, product.getDescription());
            pst.setInt(3, product.getId());
            return pst.executeUpdate();
        }catch(Exception ex){
            throw new Exception(ex);
        }
    }

    public List<String> getEmailsPartecipantiAdAsta(Integer idAsta) throws Exception {
        String query="SELECT email "
                + "FROM offer o JOIN user u ON o.buyer=u._id_user "
                + "WHERE asta=?";
        try{
            PreparedStatement pst=con.prepareStatement(query);
            pst.setInt(1, idAsta);
            ResultSet rs=pst.executeQuery();
            LinkedList<String> emails=new LinkedList<String>();
            while(rs.next()){
                emails.add(rs.getString("email"));
            }
            return emails;
        }catch(Exception ex){
            throw new Exception(ex);
        }
    }
    
    public List<Offer> getOfferOfAsta(Integer idAsta) throws Exception{
        String query="SELECT * "
                + "FROM offer "
                + "WHERE asta=? "
                + "ORDER BY date DESC";
        try{
            PreparedStatement pst=con.prepareStatement(query);
            pst.setInt(1, idAsta);
            ResultSet rs=pst.executeQuery();
            LinkedList<Offer> offers=new LinkedList<Offer>();
            while(rs.next()){
                Offer o=new Offer(
                        rs.getInt("_id_offer"),
                        rs.getFloat("actual_price"),
                        rs.getFloat("max_price"),
                        rs.getTimestamp("date").getTime(),
                        rs.getInt("buyer"),
                        rs.getInt("asta")
                        );                            
                o.setAsta(this.getAsta(o.getAstaId()));
                o.setBuyer(this.getUser(o.getBuyerId()));
                offers.add(o);
            }
            return offers;
        }catch(Exception ex){
            throw new Exception(ex);
        }
    }
    
    public List<Offer> getOfferOfUser(Integer idUser) throws Exception{
        String query="SELECT * "
                + "FROM offer "
                + "WHERE buyer=? "
                + "ORDER BY date DESC";
        try{
            PreparedStatement pst=con.prepareStatement(query);
            pst.setInt(1, idUser);
            ResultSet rs=pst.executeQuery();
            LinkedList<Offer> offers=new LinkedList<Offer>();
            while(rs.next()){
                Offer o=new Offer(
                        rs.getInt("_id_offer"),
                        rs.getFloat("actual_price"),
                        rs.getFloat("max_price"),
                        rs.getTimestamp("date").getTime(),
                        rs.getInt("buyer"),
                        rs.getInt("asta")
                        );            
                o.setAsta(this.getAsta(o.getAstaId()));
                o.setBuyer(this.getUser(o.getBuyerId()));
                offers.add(o);
            }
            return offers;
        }catch(Exception ex){
            throw new Exception(ex);
        }
    }
    
    public List<Asta> getAsteWonOfUser(Integer idUser) throws Exception{
        String query="SELECT * "
                + "FROM asta "
                + "WHERE buyer=? AND annulled=0 AND date<?"
                + "ORDER BY date DESC";
        try{
            PreparedStatement pst=con.prepareStatement(query);
            pst.setInt(1, idUser);
            java.sql.Timestamp ts=new java.sql.Timestamp(new Date().getTime());
            pst.setTimestamp(2, ts);
            ResultSet rs=pst.executeQuery();
            LinkedList<Asta> aste=new LinkedList<Asta>();
            while(rs.next()){
              Asta asta=new Asta(
                      rs.getInt("_id_asta"),
                      rs.getInt("buyer"),
                      rs.getInt("seller"),
                      rs.getInt("product"),
                      rs.getInt("quantity"),
                      
                      rs.getFloat("start_price"),
                      rs.getFloat("min_price"),
                      rs.getFloat("min_increment"),
                      rs.getFloat("shipping_price"),
                      rs.getFloat("last_offer_price"),
                      
                      (Date)rs.getObject("date"),
                      rs.getInt("um"),
                      rs.getBoolean("annulled"),
                      null
                      );
              asta.setProduct(this.getProduct(asta.getProductId()));
                asta.setSeller(this.getUser(asta.getSellerId()));
                if(asta.getBuyerId()!=null){
                    asta.setBuyer(this.getUser(asta.getBuyerId()));
                }
              aste.add(asta);
            }
            return aste;
        }catch(Exception ex){
            throw new Exception(ex);
        }
    }

    public List<Asta> getAsteLostOfUser(Integer idUser) throws Exception {
        String query="SELECT * "
                + " FROM asta a "
                + " JOIN "
                + " ( SELECT DISTINCT (asta) AS asta FROM offer WHERE buyer=?) o "
                + " ON "
                + " a._id_asta=o.asta "
                + " WHERE a.buyer<>? AND annulled=0 " //Non annullata 
                + " ORDER BY date DESC";  
        
        try{
            PreparedStatement pst=con.prepareStatement(query);
            pst.setInt(1, idUser);
            pst.setInt(2, idUser);
            ResultSet rs=pst.executeQuery();
            List<Asta> aste=new LinkedList<Asta>();
            while(rs.next()){
                Asta asta=new Asta(
                      rs.getInt("_id_asta"),
                      rs.getInt("buyer"),
                      rs.getInt("seller"),
                      rs.getInt("product"),
                      rs.getInt("quantity"),
                      
                      rs.getFloat("start_price"),
                      rs.getFloat("min_price"),
                      rs.getFloat("min_increment"),
                      rs.getFloat("shipping_price"),
                      rs.getFloat("last_offer_price"),
                      
                      (Date)rs.getObject("date"),
                      rs.getInt("um"),
                      rs.getBoolean("annulled"),
                      null
                      );
              asta.setProduct(this.getProduct(asta.getProductId()));
                asta.setSeller(this.getUser(asta.getSellerId()));
                if(asta.getBuyerId()!=null){
                    asta.setBuyer(this.getUser(asta.getBuyerId()));
                }
              
              aste.add(asta);
            }
            return aste;
        }catch(Exception ex){
            throw new Exception(ex);
        }
        
    }
    
    public Integer addOfferToDB(Offer offer){
        Integer offerId=null;
            try{
                //con.startTransuction()
                offer.setDate(new Date());
                con.setAutoCommit(false);
                PreparedStatement pst=con.prepareStatement("INSERT INTO offer ("
                    + "buyer,"
                    + "actual_price,"
                    + "max_price,"
                        
                    + "date,"
                    + "asta)"
                    + " VALUES (?,?,?, ?,?);",
                    PreparedStatement.RETURN_GENERATED_KEYS);
            pst.setInt(1, offer.getBuyerId());
            pst.setFloat(2, offer.getActualPrice());
            pst.setFloat(3, offer.getMaxPrice());
            //per inserire sia la data che il tempo:
            java.sql.Timestamp datetime = new java.sql.Timestamp(offer.getDate().getTime());
            pst.setObject(4,datetime);
            pst.setInt(5, offer.getAstaId());
            pst.executeUpdate();
            ResultSet rs = pst.getGeneratedKeys();
            try{
                if (rs.next()) {
                    offerId=rs.getInt(1);
                }
            } finally {
                rs.close();
            }
            this.updateAsta(offer.getAstaId(),offer.getBuyerId(),offer.getActualPrice());
            con.commit();
        }catch(SQLException ex){
            ex.printStackTrace();
        }finally{
            try{
                if (!con.getAutoCommit()) {
                    con.setAutoCommit(true);
                }
            }catch(Exception ex){
                throw new RuntimeException("Error diuring autocommit");
            }
        }
            return offerId;
    }
    
    //Ritorna l'email di chi ha perso
    public String addOffer(Offer offer) throws Exception {
        String email=null;
        synchronized(addOffer){
            Offer lastBetterOffer=this.getLastBetterOffer(offer.getAstaId());
            if(lastBetterOffer!=null){
                email=lastBetterOffer.getBuyer().getEmail();
            }
            Offer[] offers=getOffersToDo(offer,lastBetterOffer);
            if(offers==null){
                throw new Exception();
            }
            for(int i=0;i<offers.length;i++){
                Integer id=addOfferToDB(offers[i]);
                if(id==null){
                    throw new Exception("An error occured");
                }
                offers[i].setId(id);
            }
            if((offers.length!=1)||(offers[0].getBuyerId()!=offer.getBuyerId())){
                //C'e' un nuovo miglior offerente e avverto l'ultimo offerente
                email=offer.getBuyer().getEmail();
            }
            if((lastBetterOffer!=null)&&(lastBetterOffer.getBuyer().getEmail().equals(offer.getBuyer().getEmail()))){
                return null;
            }
        }
        return email;
    }
    
    private void updateAsta(Integer astaId, Integer buyerId, Float price) {
        try {
            PreparedStatement pst=con.prepareStatement(
                            "UPDATE asta "
                          + "SET buyer=? , last_offer_price=? "
                          + "WHERE _id_asta=? ");
            pst.setInt(1, buyerId);
            pst.setFloat(2, price);
            pst.setInt(3, astaId);
            pst.executeUpdate();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
    }

    private Offer[] getOffersToDo(Offer new_offer,Offer last_better_offert) throws Exception {
        Offer[] offers=null;
        Float minIncrement=new_offer.getAsta().getMin_increment();
        if((minIncrement==null)||(minIncrement==0)){
            minIncrement=0.1f;
        }
        if(last_better_offert==null){
            if(new_offer.getAsta().getStart_price()>new_offer.getMaxPrice()){
                throw new Exception();
            }
            offers=new Offer[1];
            Float actualPrice=new_offer.getAsta().getStart_price();
            new_offer.setActualPrice(actualPrice);
            offers[0]=new_offer;
        } else if(new_offer.getMaxPrice()>=last_better_offert.getMaxPrice()+minIncrement){
            //La nuova offerta e' maggiore e supera con l'incremento minimo l'ultima
            offers=new Offer[1];
            Float actualPrice=last_better_offert.getMaxPrice()+minIncrement;
            new_offer.setActualPrice(actualPrice);
            offers[0]=new_offer;
        } else if(new_offer.getMaxPrice()+minIncrement<=last_better_offert.getMaxPrice()){
            offers=new Offer[2];
            new_offer.setActualPrice(new_offer.getMaxPrice());
            offers[0]=new_offer;
            last_better_offert.setActualPrice(new_offer.getMaxPrice()+minIncrement);
            offers[1]=last_better_offert;  
        }else{
            //L'offerta massima non e' superiore della nuova offerta + l'incremento
            // e l'offerta attuale non e' superiore dell'offerta attuale piu' l'incremento
            // dico che l'offerta non e' valida perche' il prezzo e' troppo basso
        }
        return offers;
    }

    public Offer getLastBetterOffer(Integer astaId) throws Exception {
        String query=""
                + "SELECT * FROM offer "
                + "WHERE asta=? AND _id_offer=( "
                + "SELECT MAX(_id_offer) "
                + "FROM offer "
                + "WHERE asta=?"
                + ")";
        try{
            PreparedStatement pst=con.prepareStatement(query);
            pst.setInt(1, astaId);
            pst.setInt(2, astaId);
            ResultSet rs=pst.executeQuery();
            Offer offer=null;
            if(rs.next()){
                offer=new Offer(
                        rs.getInt("_id_offer"),
                        rs.getFloat("actual_price"),
                        rs.getFloat("max_price"),
                        rs.getTimestamp("date").getTime(),
                        rs.getInt("buyer"),
                        rs.getInt("asta")
                        );    
                offer.setAsta(this.getAsta(offer.getAstaId()));
                offer.setBuyer(this.getUser(offer.getBuyerId()));        
            }
            return offer;
        }catch(Exception ex){
            throw new Exception(ex);
        }
    }

    public Offer getOffer(Integer id) throws Exception {
        String query=""
                + "SELECT * FROM offer "
                + "_id_offer?";
        try{
            PreparedStatement pst=con.prepareStatement(query);
            pst.setInt(1, id);
            ResultSet rs=pst.executeQuery();
            Offer offer=null;
            if(rs.next()){
                offer=new Offer(
                        rs.getInt("_id_offer"),
                        rs.getFloat("actual_price"),
                        rs.getFloat("max_price"),
                        rs.getTimestamp("date").getTime(),
                        rs.getInt("buyer"),
                        rs.getInt("asta")
                        );    
                offer.setAsta(this.getAsta(offer.getAstaId()));
                offer.setBuyer(this.getUser(offer.getBuyerId()));        
            }
            return offer;
        }catch(Exception ex){
            throw new Exception(ex);
        }
    }
    
    
    public ArrayList<Asta> getAsteAttiveFromProduct(Integer prId) throws Exception{
        ArrayList<Asta> aste=new ArrayList<Asta>();
        try {
            PreparedStatement pst=con.prepareStatement("SELECT *"
                    + " FROM asta"
                    + " WHERE product=? AND date > ? "
                    );
            
            pst.setInt(1, prId);
            
            //per inserire sia la data che il tempo:
            Calendar calendar = Calendar.getInstance();
            java.sql.Timestamp datetime = new java.sql.Timestamp(calendar.getTime().getTime());
            pst.setObject(2,datetime);
            
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                Asta a=new Asta(
                      rs.getInt("_id_asta"),
                      rs.getInt("buyer"),
                      rs.getInt("seller"),
                      rs.getInt("product"),
                      rs.getInt("quantity"),
                      
                      rs.getFloat("start_price"),
                      rs.getFloat("min_price"),
                      rs.getFloat("min_increment"),
                      rs.getFloat("shipping_price"),
                      rs.getFloat("last_offer_price"),
                      
                      (Date)rs.getObject("date"),
                      rs.getInt("um"),
                      rs.getBoolean("annulled"),
                      null
                      );
                a.setProduct(this.getProduct(a.getProductId())); 
                a.setSeller(this.getUser(a.getSellerId()));
                if(a.getBuyerId()!=null){
                    a.setBuyer(this.getUser(a.getBuyerId()));
                }
                 aste.add(a);
            }
        } catch (SQLException ex) {
            throw new Exception(ex);
        }
        return aste;
        
    }
    
    public ArrayList<User> getTopSellers() throws Exception{
        ArrayList sellers=new ArrayList<User>();
        try{
            PreparedStatement pst=con.prepareStatement("SELECT seller, SUM(last_offer_price) AS pay \n" +
                    "FROM ( SELECT seller, last_offer_price \n" +
                    "FROM asta \n" +
                    "WHERE annulled=0 AND last_offer_price IS NOT NULL AND date <= ? \n" +
                    ") AS aste GROUP BY seller ORDER BY pay DESC LIMIT 10;" );
            
            //per inserire sia la data che il tempo:
            Calendar calendar = Calendar.getInstance();
            java.sql.Timestamp datetime = new java.sql.Timestamp(calendar.getTime().getTime());
            pst.setObject(1,datetime);
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                User user=this.getUser(rs.getInt("seller"));
                sellers.add(user);
            }
        }catch(Exception ex){
            throw new Exception(ex);
        }
        return sellers;
    }

    

//COMPRATORE
//Quello che la cui somma delle offerte delle aste aggiudicate sia massima
public ArrayList<User> getTopBuyers() throws Exception{
        ArrayList buyers=new ArrayList<User>();
        try{
            PreparedStatement pst=con.prepareStatement("SELECT buyer, SUM(last_offer_price) AS pay \n" +
                        "FROM ( SELECT buyer, last_offer_price \n" +
                        "FROM asta \n" +
                        "WHERE annulled=0 AND last_offer_price IS NOT NULL AND date <= ?\n" +
                        ") AS aste GROUP BY buyer ORDER BY pay LIMIT 10;" );
            
            //per inserire sia la data che il tempo:
            Calendar calendar = Calendar.getInstance();
            java.sql.Timestamp datetime = new java.sql.Timestamp(calendar.getTime().getTime());
            pst.setObject(1,datetime);
            
            ResultSet rs=pst.executeQuery();
                                    
            while(rs.next()){
                User user=this.getUser(rs.getInt("buyer"));
                buyers.add(user);
            }
        }catch(Exception ex){
            throw new Exception(ex);
        }
        return buyers;
    }    
    
public ArrayList<Product> searchProducts(String strPattern, Integer category,String sellerName,Integer sellerId) {
        ArrayList<Product> products=new ArrayList<Product>();
        try{
            
            
            PreparedStatement pst=prepareSearchStatement(strPattern,category,sellerName,sellerId);
            
            
            
            int index=1;
            if(strPattern!=null){
                //Description
                pst.setString(index, strPattern);
                index++;
                //Titolo
                pst.setString(index, strPattern);
                index++;
            }
            if(category!=null){
                pst.setInt(index, category);
                index++;
            }
            
            if(sellerName!=null){
                pst.setString(index, sellerName);
                index++;
            }
            if(sellerId!=null){
                pst.setInt(index, sellerId);
            }
            ResultSet rs=pst.executeQuery();
            while(rs.next()){
                
                Product product=new Product(
                      rs.getInt("_id_product"),
                      rs.getString("name"),
                      rs.getInt("quantity"),
                      rs.getInt("um_id"),
                      rs.getString("um_name"),
                      rs.getInt("image_id"),
                      rs.getString("image_name"),
                      rs.getString("description"),
                      rs.getInt("seller_id"),
                      rs.getString("seller_name"),
                      rs.getInt("category_id"),
                      rs.getString("category_name")
                      );
                products.add(product);
            }
        }catch(Exception ex){
            throw new RuntimeException(ex);
        }        
        return products;
    }

    private PreparedStatement prepareSearchStatement(String strPattern, Integer category, String sellerName,Integer sellerId) 
            throws SQLException {
        String query="SELECT P._id_product, P.name,  P.quantity, P.um AS um_id, "
                + "um.name AS um_name, "
                + "I._id_image AS image_id,"
                + "I.name AS image_name,"
                + "P.description,"
                + "P.seller AS seller_id,"
                + "P.category AS category_id,"
                + " username AS seller_name,"
                + "C.name AS category_name "
                //+ "(first_name || ' ' || last_name ) AS seller_name"
                + " FROM user, product P, category C, um, image I "
                + " WHERE P.category=_id_category AND _id_user=P.seller"
                + " AND P.um=_id_um AND P.image=I._id_image ";
        if(strPattern!=null){
            query=query+" AND ( P.description REGEXP ? ";
            query=query+" OR P.name REGEXP ? ) ";
        }
        if(category!=null){
            query=query+" AND P.category=?  ";
        }
        if(sellerName!=null){
            //query=query+"seller= (SELECT _id_user FROM user WHERE first_name || ' ' || last_name like ? )  AND ";
            query=query+" AND P.seller= (SELECT _id_user FROM user WHERE username like ? ) ";
        }
        if(sellerId!=null){
            query=query+" AND P.seller=?     ";
        }
        //query=query.substring(0,query.length()-5);
       
        System.out.println(""+query);
        
        return con.prepareStatement(query);
    }

}